Join Data Tables

AutomatR.DefaultActivities.Datatable.JoinDataTables

The "Join Data Tables" activity in AutomatR allows you to combine rows from two DataTables by matching values that are common to each other, following a specified Join rule. This activity facilitates merging data from different sources based on matching column values.

Properties

Name Description

Input

Data Table 1 Specifies the first DataTable required to perform the join operation. Variable or argument containing the first DataTable.

Data Table 2 Specifies the second DataTable required to perform the join operation. Variable or argument containing the second DataTable.

Data Table 1 Column Name Specifies the column name from the first DataTable to be used for the join operation. String variable or argument containing the column name.

Data Table 2 Column Name Specifies the column name from the second DataTable to be used for the join operation. String variable or argument containing the column name.

Join Type Selects the type of join based on the operation required and the nature of joins. Enum variable or argument. Options: inner_join, outer_join, left_join, right_join.

left_join : Places all the rows from the left table and any matching rows from the right table.
Refer the below example:
DataTable1

Customer ID	Customer Name
1	John
2	Sarah
3	David

DataTable2

Order ID	Customer ID	Product
101	1	Laptop
102	2	Headphones
103	1	Smartphone

The left_join result would be:

Customer ID	Customer Name	Order ID	Product
1	John	101	Laptop
1	John	103	Smartphone
2	Sarah	102	Headphones
3	David

As you can see, all the rows from the left of DataTable1 are included, and the matching rows from the right of DataTable2 are joined based on the common "CustomerID" column. If there is no match, the corresponding values from the right table are filled with empty or null values, as seen in the last row of the result.

right_join : Places all the rows from the right table and any matching rows from the left table.
Refer the below example:
DataTable1

Customer ID	Customer Name
1	John
2	Sarah
3	David

DataTable2

Order ID	Customer ID	Product
101	1	Laptop
102	2	Headphones
103	1	Smartphone

The right_join result would be:

Customer ID	Customer Name	Order ID	Product
1	John	101	Laptop
1	John	103	Smartphone
2	Sarah	102	Headphones

As you can see, all the rows from the right of DataTable2 are included, and the matching rows from the left of DataTable1 are joined based on the common "CustomerID" column. If there is no match, the corresponding values from the left table are filled with empty or null values.

inner_join : Only the rows that have matching values in the specified key column from both tables are included in the resulting dataset. The non-matching rows from either table are excluded.
Refer the below example:
DataTable1

Customer ID	Customer Name
1	John
2	Sarah
3	David

DataTable2

Order ID	Customer ID	Product
101	1	Laptop
102	2	Headphones
103	1	Smartphone
104	4	Tablet

The inner_join result would be:

Customer ID	Customer Name	Order ID	Product
1	John	101	Laptop
1	John	103	Smartphone
2	Sarah	102	Headphones

As you can see, only the rows with matching "CustomerID" values from both tables are included in the result. The row with CustomerID 3 from the DataTable1 and the row with OrderID 104 from the DataTable2, which don't have matching values, are excluded from the result.

outer_join : All the rows from both tables are included in the resulting dataset, regardless of whether they have matching values in the specified key column.
Refer the below example:
DataTable1

Customer ID	Customer Name
1	John
2	Sarah
3	David

DataTable2

Order ID	Customer ID	Product
101	1	Laptop
102	2	Headphones
103	1	Smartphone
104	4	Tablet

The outer_join result would be:

Customer ID	Customer Name	Order ID	Product
1	John	101	Laptop
1	John	103	Smartphone
2	Sarah	102	Headphones
3	David
4		104	Tablet

As you can see, all the rows from both the "DataTable1" and "DataTable2" tables are included in the result. If there is no match, the corresponding values are filled with null or empty values.

Misc

Display Name Provides a customizable name for the activity displayed in the workflow. The display name enhances clarity and organization within the automation project. String variable or argument containing the desired display name.

Optional

Delay Specifies the amount of time (in seconds) to wait before executing the "Join Data Tables" activity. This can be useful for handling synchronization issues. Integer variable or argument containing the delay duration. Example: If the amount of time is 1000 milliseconds or 1 sec, i.e., 1.

Output

Result Outputs the DataTable after the join operation based on the join type and both input DataTables. Variable or argument of type DataTable to store the result of the join operation.

How to use:

Drag and drop the "Join Data Tables" activity onto the workflow.
Configure the properties by specifying the first DataTable, second DataTable, column names, join type, and optionally customize the display name.
Execute the workflow to perform the join operation, and the result will be stored in the specified variable.

Example: Consider an example where the "Join Data Tables" activity is used to combine data from two DataTables based on a common column:

Join Data Tables:
  Display Name: "Combine Data"
  Data Table 1: dataTable1Variable
  Data Table 2: dataTable2Variable
  Data Table 1 Column Name: "ID"
  Data Table 2 Column Name: "ID"
  Join Type: inner_join
  Result: joinedDataTable

In this example, the activity combines rows from dataTable1Variable and dataTable2Variable based on the common column "ID" using an inner join. The result of the operation is stored in the DataTable variable "joinedDataTable" for further use in the workflow.

Join Data Tables

Properties​

Properties